package com.example.demo3;

import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.ResponseBody;

import javax.servlet.http.HttpServletResponse;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.net.URLEncoder;
import java.util.*;

/**
 * @author QuiFar
 * @version V1.0
 **/
@Controller
public class IndexController {

    @GetMapping("/index")
    @ResponseBody
    public String index(HttpServletResponse response) throws IOException {

        // 模拟数据
        List<Map<String, String>> listData = listData();

        File fi = new File("E:/inpatient.xls");
        POIFSFileSystem fs = null;
        HSSFWorkbook wb = null;
        OutputStream out = null;
        try {
            fs = new POIFSFileSystem(new FileInputStream(fi));

            //读取excel模板
            wb = new HSSFWorkbook(fs);

        } catch (IOException e) {
            //wb = new XSSFWorkbook();
        }

        // 生产sheet表格数量
        for (int i = 0; i < 3; i++) {
            //读取了模板内所有sheet内容
            HSSFSheet sheet = wb.getSheetAt(i);

            CellStyle style = wb.createCellStyle();

            wb.setSheetName(i, "名字" + i);

            // 替换值
            Map<String, String> mapData = new HashMap<>();
            mapData.put("companyName", "公司名称" + i);
            mapData.put("department", "科室名称" + i);
            mapData.put("listSize", "100");
            mapData.put("createTime", "2018-1-30");
            ExcelUtil.setCellValue(sheet, mapData);

            int dataSize = listData.size();
            if (dataSize > 20) {
                this.excelListDataA4(sheet, style, listData);
            } else {
                this.excelListDataA5(sheet, style, listData);
            }


        }

        response.setContentType("application/vnd.ms-excel");
        response.setHeader("Content-disposition", "attachment;filename=" + URLEncoder.encode("cc.xls", "utf-8"));
        out = response.getOutputStream();

        //remove empty sheet
        removeEmptySheet(wb, 3);

        wb.write(out);

        out.flush();
        out.close();
        wb.close();

        return "hello world";
    }

    /**
     * 批量移除空的工作簿
     * <p>需要注意的是 移除后工作的索引为最后一个</p>
     *
     * @param wb
     */
    private void removeEmptySheet(HSSFWorkbook wb, int startIndex) {
        for (int i = 4; i < 21; i++) {
            wb.removeSheetAt(startIndex);
        }
    }


    private List<Map<String, String>> listData() {
        List<Map<String, String>> list = new ArrayList<>();

        //模拟数据
        Map<String, String> data1 = new LinkedHashMap<>();
        data1.put("num", "1");
        data1.put("orderId", "10001");
        data1.put("bedNum", "01");
        data1.put("name", "张三");
        data1.put("count", "10");
        data1.put("method", "吃");

        Map<String, String> data2 = new LinkedHashMap<>();
        data2.put("num", "2");
        data2.put("orderId", "10002");
        data2.put("bedNum", "02");
        data2.put("name", "李四");
        data2.put("count", "4");
        data2.put("method", "吃");

        Map<String, String> data3 = new LinkedHashMap<>();
        data3.put("num", "3");
        data3.put("orderId", "10003");
        data3.put("bedNum", "02");
        data3.put("name", "王五");
        data3.put("count", "5");
        data3.put("method", "sss");

        Map<String, String> data4 = new LinkedHashMap<>();
        data4.put("num", "4");
        data4.put("orderId", "10004");
        data4.put("bedNum", "02");
        data4.put("name", "王五");
        data4.put("count", "4");
        data4.put("method", "sss");

        Map<String, String> data5 = new LinkedHashMap<>();
        data5.put("num", "5");
        data5.put("orderId", "10005");
        data5.put("bedNum", "05");
        data5.put("name", "王五");
        data5.put("count", "5");
        data5.put("method", "sss");

        Map<String, String> data6 = new LinkedHashMap<>();
        data6.put("num", "6");
        data6.put("orderId", "10006");
        data6.put("bedNum", "06");
        data6.put("name", "王五");
        data6.put("count", "6");
        data6.put("method", "sss");

        Map<String, String> data7 = new LinkedHashMap<>();
        data7.put("num", "7");
        data7.put("orderId", "10007");
        data7.put("bedNum", "07");
        data7.put("name", "王五");
        data7.put("count", "7");
        data7.put("method", "sss");

        Map<String, String> data8 = new LinkedHashMap<>();
        data8.put("num", "8");
        data8.put("orderId", "10008");
        data8.put("bedNum", "08");
        data8.put("name", "王五");
        data8.put("count", "8");
        data8.put("method", "sss");

        Map<String, String> data9 = new LinkedHashMap<>();
        data9.put("num", "9");
        data9.put("orderId", "10009");
        data9.put("bedNum", "09");
        data9.put("name", "王五");
        data9.put("count", "9");
        data9.put("method", "sss");

        Map<String, String> data10 = new LinkedHashMap<>();
        data10.put("num", "10");
        data10.put("orderId", "10010");
        data10.put("bedNum", "10");
        data10.put("name", "王五");
        data10.put("count", "10");
        data10.put("method", "sss");

        Map<String, String> data11 = new LinkedHashMap<>();
        data11.put("num", "11");
        data11.put("orderId", "10011");
        data11.put("bedNum", "11");
        data11.put("name", "王五");
        data11.put("count", "11");
        data11.put("method", "sss");

        Map<String, String> data12 = new LinkedHashMap<>();
        data12.put("num", "12");
        data12.put("orderId", "10012");
        data12.put("bedNum", "12");
        data12.put("name", "王五");
        data12.put("count", "12");
        data12.put("method", "sss");


        Map<String, String> data13 = new LinkedHashMap<>();
        data13.put("num", "13");
        data13.put("orderId", "10013");
        data13.put("bedNum", "13");
        data13.put("name", "王五");
        data13.put("count", "13");
        data13.put("method", "sss");


        Map<String, String> data14 = new LinkedHashMap<>();
        data14.put("num", "14");
        data14.put("orderId", "10014");
        data14.put("bedNum", "14");
        data14.put("name", "王五");
        data14.put("count", "14");
        data14.put("method", "sss");

        Map<String, String> data15 = new LinkedHashMap<>();
        data15.put("num", "15");
        data15.put("orderId", "10015");
        data15.put("bedNum", "15");
        data15.put("name", "王五");
        data15.put("count", "15");
        data15.put("method", "sss");

        Map<String, String> data16 = new LinkedHashMap<>();
        data16.put("num", "16");
        data16.put("orderId", "10016");
        data16.put("bedNum", "16");
        data16.put("name", "王五");
        data16.put("count", "16");
        data16.put("method", "sss");

        Map<String, String> data17 = new LinkedHashMap<>();
        data17.put("num", "17");
        data17.put("orderId", "10017");
        data17.put("bedNum", "17");
        data17.put("name", "王五");
        data17.put("count", "17");
        data17.put("method", "sss");

        Map<String, String> data18 = new LinkedHashMap<>();
        data18.put("num", "18");
        data18.put("orderId", "10018");
        data18.put("bedNum", "18");
        data18.put("name", "王五");
        data18.put("count", "18");
        data18.put("method", "sss");

        Map<String, String> data19 = new LinkedHashMap<>();
        data19.put("num", "19");
        data19.put("orderId", "10019");
        data19.put("bedNum", "19");
        data19.put("name", "王五");
        data19.put("count", "19");
        data19.put("method", "sss");

        Map<String, String> data20 = new LinkedHashMap<>();
        data20.put("num", "20");
        data20.put("orderId", "10020");
        data20.put("bedNum", "20");
        data20.put("name", "王五");
        data20.put("count", "20");
        data20.put("method", "sss");

        Map<String, String> data21 = new LinkedHashMap<>();
        data21.put("num", "21");
        data21.put("orderId", "10021");
        data21.put("bedNum", "20");
        data21.put("name", "王五");
        data21.put("count", "21");
        data21.put("method", "sss");

        Map<String, String> data22 = new LinkedHashMap<>();
        data22.put("num", "22");
        data22.put("orderId", "10022");
        data22.put("bedNum", "22");
        data22.put("name", "王五");
        data22.put("count", "22");
        data22.put("method", "sss");

        Map<String, String> data23 = new LinkedHashMap<>();
        data23.put("num", "23");
        data23.put("orderId", "10023");
        data23.put("bedNum", "20");
        data23.put("name", "王五");
        data23.put("count", "23");
        data23.put("method", "sss");

        Map<String, String> data24 = new LinkedHashMap<>();
        data24.put("num", "24");
        data24.put("orderId", "10024");
        data24.put("bedNum", "20");
        data24.put("name", "王五");
        data24.put("count", "23");
        data24.put("method", "sss");

        Map<String, String> data25 = new LinkedHashMap<>();
        data25.put("num", "25");
        data25.put("orderId", "10025");
        data25.put("bedNum", "20");
        data25.put("name", "王五");
        data25.put("count", "23");
        data25.put("method", "sss");

        Map<String, String> data26 = new LinkedHashMap<>();
        data26.put("num", "26");
        data26.put("orderId", "10026");
        data26.put("bedNum", "20");
        data26.put("name", "王五");
        data26.put("count", "23");
        data26.put("method", "sss");

        list.add(data1);
        list.add(data2);
        list.add(data3);
        list.add(data4);
        list.add(data5);
        list.add(data6);
        list.add(data7);
        list.add(data8);
        list.add(data9);
        list.add(data10);
        list.add(data11);
        list.add(data12);
        list.add(data13);
        list.add(data14);
        list.add(data15);
        list.add(data16);
        list.add(data17);
        list.add(data18);
        list.add(data19);
        list.add(data20);
        list.add(data21);
        list.add(data22);
        list.add(data23);
        list.add(data24);
        list.add(data25);
        list.add(data26);


        return list;
    }


    /**
     * 为单张表格赋值(A5模板，列表显示20条数据)
     *
     * @param sheet
     * @param listData
     */
    private void excelListDataA5(HSSFSheet sheet, CellStyle style, List<Map<String, String>> listData) {
        // 从第8行开始打印列表
        int j = 7;
        // 单元格是否从0 开始
        boolean isCellZero = true;
        // 是否从0开始
        boolean isZeroFlag = true;
        HSSFRow row;
        for (Map<String, String> obj : listData) {

            // 显示10条数据后换列
            if (j >= 17) {
                isCellZero = false;
                isZeroFlag = true;
            }

            //如果j < 17,单元格从0开始,否则从7开始
            if (isCellZero) {
                row = sheet.getRow(j);
                ExcelUtil.setCellValue(row, style, obj);
            } else {
                // 从头开始
                if (isZeroFlag) {
                    j = 7;
                }
                isZeroFlag = false;

                row = sheet.getRow(j);
                ExcelUtil.setCellValueChangeLine(row, 6, style, obj);
            }

            j++;
        }


        // 设置表格边框
        //ExcelUtil.setCellStyleA5(style,sheet);


    }

    /**
     * 为单张表格赋值(A4模板，列表显示50条数据)
     *
     * @param sheet
     * @param listData
     */
    private void excelListDataA4(HSSFSheet sheet, CellStyle style, List<Map<String, String>> listData) {

        // 先创建数据列表的行和单元格，并加上边框
        // 设置表格边框
        ExcelUtil.setCellStyleA4(style, sheet);

        // 从第8行开始打印列表
        int j = 7;
        // 单元格是否从0 开始
        boolean isCellZero = true;
        // 是否从0开始
        boolean isZeroFlag = true;
        HSSFRow row;
        for (Map<String, String> obj : listData) {

            // 显示25条数据后换列
            if (j >= 32) {
                isCellZero = false;
                isZeroFlag = true;
            }

            //如果j < 32,单元格从0开始,否则从7开始
            if (isCellZero) {
                row = sheet.getRow(j);
                ExcelUtil.setCellValue(row, style, obj);


            } else {
                // 从头开始
                if (isZeroFlag) {
                    j = 7;
                }
                isZeroFlag = false;

                row = sheet.getRow(j);
                ExcelUtil.setCellValueChangeLine(row, 6, style, obj);

            }

            j++;
        }


        /*// 从第8行开始打印列表
        int rowNum = 7;
        // 单元格是否从0 开始
        boolean isCellZero = true;
        // 是否从0开始
        boolean isZeroFlag = true;
        HSSFRow row;
        for (Map<String, String> obj : listData) {

            // 显示25条数据后换列
            if (rowNum >= 32) {
                isCellZero = false;
                isZeroFlag = true;
            }

            //如果j < 32,单元格从0开始,否则从7开始
            if (isCellZero) {
                row = sheet.createRow(rowNum);
                int i = 0;
                for (String k : obj.keySet()) {
                    Cell cell = row.createCell(i);
                    cell.setCellValue(String.valueOf(obj.get(k)));
                    i++;
                }


            } else {
                // 从头开始
                if (isZeroFlag) {
                    rowNum = 7;
                }

                isZeroFlag = false;
                row = sheet.getRow(rowNum);
                int i = 6;
                for (String k : obj.keySet()) {
                    Cell cell = row.createCell(i);
                    cell.setCellValue(String.valueOf(obj.get(k)));
                    i++;
                }

            }

            rowNum++;
        }*/


    }


}
